﻿

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using MySql.Data.MySqlClient;

public class DAL_tbl_siconide_palla
{
    public DAL_tbl_siconide_palla()
    {

    }



    public List<tbl_siconide_palla> select(int ID)
    {
        List<tbl_siconide_palla> list = new List<tbl_siconide_palla>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Mill,Feed_Rate,RPM,Room_Temperature,Palla_humidity,Remark,Previous_Process,CHARGE_Number,FLOWBIN_Number,MILLJET_RUN_NUMBER,PALLA_RUN_NUMBER,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconide_palla where ID=@ID";
        cmd.Parameters.AddWithValue("@ID", ID);
        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconide_palla()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Mill = Convert.ToInt32(dr["Mill"]),
                Feed_Rate = Convert.ToDecimal(dr["Feed_Rate"]),
                RPM = Convert.ToDecimal(dr["RPM"]),
                Room_Temperature = Convert.ToDecimal(dr["Room_Temperature"]),
                Palla_humidity = Convert.ToDecimal(dr["Palla_humidity"]),
                Remark = dr["Remark"].ToString(),
                Previous_Process = dr["Previous_Process"].ToString(),
                CHARGE_Number=Convert.ToDecimal(dr["CHARGE_Number"]),
                FLOWBIN_Number=Convert.ToDecimal(dr["FLOWBIN_Number"]),
                MILLJET_RUN_NUMBER=dr["MILLJET_RUN_NUMBER"].ToString(),
                PALLA_RUN_NUMBER =dr["PALLA_RUN_NUMBER"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }



    public List<tbl_siconide_palla> selectAll()
    {
        List<tbl_siconide_palla> list = new List<tbl_siconide_palla>();
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = "Select ID,Mill,Feed_Rate,RPM,Room_Temperature,Palla_humidity,Remark,Previous_Process,CHARGE_Number,FLOWBIN_Number,MILLJET_RUN_NUMBER,PALLA_RUN_NUMBER,IS_ACTIVE,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE from tbl_siconide_palla Order By ID";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        foreach (DataRow dr in dt.Rows)
        {
            list.Add(new tbl_siconide_palla()
            {
                ID = Convert.ToInt32(dr["ID"]),
                Mill = Convert.ToInt32(dr["Mill"]),
                Feed_Rate = Convert.ToDecimal(dr["Feed_Rate"]),
                RPM = Convert.ToDecimal(dr["RPM"]),
                Room_Temperature = Convert.ToDecimal(dr["Room_Temperature"]),
                Palla_humidity = Convert.ToDecimal(dr["Palla_humidity"]),
                Remark = dr["Remark"].ToString(),
                Previous_Process = dr["Previous_Process"].ToString(),
                CHARGE_Number = Convert.ToDecimal(dr["CHARGE_Number"]),
                FLOWBIN_Number = Convert.ToDecimal(dr["FLOWBIN_Number"]),
                MILLJET_RUN_NUMBER = dr["MILLJET_RUN_NUMBER"].ToString(),
                PALLA_RUN_NUMBER = dr["PALLA_RUN_NUMBER"].ToString(),
                IS_ACTIVE = Convert.ToBoolean(dr["IS_ACTIVE"]),
                CREATED_BY = dr["CREATED_BY"].ToString(),
                CREATED_DATE = dr["CREATED_DATE"].toDateTimeWithoutError(),
                UPDATED_BY = dr["UPDATED_BY"].ToString(),
                UPDATED_DATE = dr["UPDATED_DATE"].toDateTimeWithoutError()
            });
        }

        return list;
    }

    public DataTable selectAllForGrid()
    {
        MySqlCommand cmd = new MySqlCommand();
        cmd.CommandText = @"Select sp.ID,mm.SHORT_NAME as Mill,sp.Feed_Rate,sp.RPM,Room_Temperature,Palla_humidity,sp.Remark,sp.Previous_Process,
sp.CHARGE_Number,sp.FLOWBIN_Number,sp.MILLJET_RUN_NUMBER,sp.PALLA_RUN_NUMBER
,CASE WHEN sp.IS_ACTIVE =1 THEN 'True' ELSE 'False' END as 'IS_ACTIVE',
sp.CREATED_BY,sp.CREATED_DATE as CREATED_DATE,sp.UPDATED_BY,
sp.UPDATED_DATE as UPDATED_DATE 
from tbl_siconide_palla sp LEFT JOIN tbl_mills_master mm on mm.MILL_ID=sp.Mill Order By sp.ID desc";

        DataTable dt = DBUtil.ExecuteDataTable(cmd);
        return dt;
    }

    public int insert(int Mill, Decimal Feed_Rate, Decimal RPM, Decimal Room_Temperature, Decimal Palla_humidity, String Remark, String Previous_Process, Decimal CHARGE_Number, Decimal FLOWBIN_Number, String MILLJET_RUN_NUMBER, String PALLA_RUN_NUMBER, String CREATED_BY, DateTime CREATED_DATE)
    {
        int i = 0;
        string sql = @"Insert into tbl_siconide_palla(Mill, Feed_Rate, RPM, Room_Temperature, Palla_humidity, Remark, Previous_Process,CHARGE_Number,FLOWBIN_Number,MILLJET_RUN_NUMBER,PALLA_RUN_NUMBER, CREATED_BY, CREATED_DATE)
                        values(@Mill, @Feed_Rate, @RPM, @Room_Temperature, @Palla_humidity, @Remark, @Previous_Process,@CHARGE_Number,@FLOWBIN_Number,@MILLJET_RUN_NUMBER,@PALLA_RUN_NUMBER, @CREATED_BY, @CREATED_DATE)";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@Mill", Mill);
        cmd.Parameters.AddWithValue("@Feed_Rate", Feed_Rate);
        cmd.Parameters.AddWithValue("@RPM", RPM);
        cmd.Parameters.AddWithValue("@Room_Temperature", Room_Temperature);
        cmd.Parameters.AddWithValue("@Palla_humidity", Palla_humidity);
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@Previous_Process", Previous_Process);
        cmd.Parameters.AddWithValue("@CHARGE_Number", CHARGE_Number);
        cmd.Parameters.AddWithValue("@FLOWBIN_Number", FLOWBIN_Number);
        cmd.Parameters.AddWithValue("@MILLJET_RUN_NUMBER", MILLJET_RUN_NUMBER);
        cmd.Parameters.AddWithValue("@PALLA_RUN_NUMBER", PALLA_RUN_NUMBER);
        cmd.Parameters.AddWithValue("@CREATED_BY", CREATED_BY);
        cmd.Parameters.AddWithValue("@CREATED_DATE", CREATED_DATE);
        
        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int update(int ID, int Mill, Decimal Feed_Rate, Decimal RPM, Decimal Room_Temperature, Decimal Palla_humidity, String Remark, String Previous_Process, Decimal CHARGE_Number, Decimal FLOWBIN_Number, String MILLJET_RUN_NUMBER, String PALLA_RUN_NUMBER, String UPDATED_BY, DateTime UPDATED_DATE)
    {
        int i = 0;
        string sql = @"Update tbl_siconide_palla SET Mill=@Mill, Feed_Rate=@Feed_Rate, RPM=@RPM, Room_Temperature=@Room_Temperature, Palla_humidity=@Palla_humidity, Remark=@Remark, Previous_Process=@Previous_Process, CHARGE_Number=@CHARGE_Number, FLOWBIN_Number=@FLOWBIN_Number, MILLJET_RUN_NUMBER=@MILLJET_RUN_NUMBER, PALLA_RUN_NUMBER=@PALLA_RUN_NUMBER, UPDATED_BY=@UPDATED_BY, UPDATED_DATE=@UPDATED_DATE WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);
        cmd.Parameters.AddWithValue("@Mill", Mill);
        cmd.Parameters.AddWithValue("@Feed_Rate", Feed_Rate);
        cmd.Parameters.AddWithValue("@RPM", RPM);
        cmd.Parameters.AddWithValue("@Room_Temperature", Room_Temperature);
        cmd.Parameters.AddWithValue("@Palla_humidity", Palla_humidity);
        cmd.Parameters.AddWithValue("@Remark", Remark);
        cmd.Parameters.AddWithValue("@Previous_Process", Previous_Process);
        cmd.Parameters.AddWithValue("@CHARGE_Number", CHARGE_Number);
        cmd.Parameters.AddWithValue("@FLOWBIN_Number", FLOWBIN_Number);
        cmd.Parameters.AddWithValue("@MILLJET_RUN_NUMBER", MILLJET_RUN_NUMBER);
        cmd.Parameters.AddWithValue("@PALLA_RUN_NUMBER", PALLA_RUN_NUMBER);
        cmd.Parameters.AddWithValue("@UPDATED_BY", UPDATED_BY);
        cmd.Parameters.AddWithValue("@UPDATED_DATE", UPDATED_DATE);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }



    public int delete(int ID)
    {
        int i = 0;
        string sql = "Delete FROM tbl_siconide_palla WHERE ID=@ID";

        MySqlCommand cmd = new MySqlCommand(sql);

        cmd.Parameters.AddWithValue("@ID", ID);

        i = DBUtil.ExecuteNonQuery(cmd);
        return i;
    }


    public DataTable GetChargeNumber(string Previous_Process)
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            if (Previous_Process == "Crushing")
            {
                cmd.CommandText = "select ID,Charge_Number from tbl_siconide_crushing where IS_ACTIVE=1";
            }
            else
            {
                cmd.CommandText = "select ID,Charge_Number from tbl_siconide_milljet where IS_ACTIVE=1";
            }

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

    public DataTable GetFlowbinNumber(string Previous_Process)
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            if (Previous_Process == "Crushing")
            {
                cmd.CommandText = "select ID,Flowbin_Number from tbl_siconide_crushing where IS_ACTIVE=1";
            }
            else
            {
                cmd.CommandText = "select ID,Flowbin_Number from tbl_siconide_milljet where IS_ACTIVE=1";
            }

            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

    public DataTable GetMillJet_Run_Number()
    {
        DataTable dt = new DataTable();
        try
        {
            MySqlCommand cmd = new MySqlCommand();
            cmd.CommandText = "select ID,MILLJET_RUN_NUMBER from tbl_siconide_milljet where IS_ACTIVE=1";
            dt = DBUtil.ExecuteDataTable(cmd);
            return dt;
        }
        catch (Exception)
        {

            throw;
        }
        finally { dt = null; }

    }

}